using INTEX.DataBase;
using ReflectionStudio.Core.Events;
using System;
using System.Data;
using System.Windows;
using System.Windows.Data;
using INTEX.DataBase.Tools;
using System.Collections;
using System.Collections.Generic;
using System.Threading;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
namespace WPFExcelReport
{
    public partial class VICC_EXORD
    {
        VICC_MAXORD MAXO;
        public string sql = "";
        public override void SetUp()
        {
            base.SetUp();
            dbConnection = app.SQLITEDB;
            SQLiteDatabase = new SQLiteDatabase(dbConnection);
        }

        public DataRow NEWVKP(Dictionary<string, object> pm)
        {
            DataRow dwInput = null;
            if (pm.ContainsKey("dwInput"))
            {
                dwInput = (DataRow)pm["dwInput"];
            }
            try
            {
                dwData = TBMain.NewRow();
                SetUpNewRow(ref dwData);
                if (dwInput != null)
                {
                    dwData.ItemArray = dwInput.ItemArray;
                }
                else
                {
                    dwData["NUMBER1"] = pm["USD2RMB"].ToString();
                    dwData["NUMBER2"] = pm["RATE1"].ToString();
                    dwData["NUMBER3"] = "0";
                    dwData["NUMBER4"] = "0";
                    dwData["NUMBER5"] = "0";
                    dwData["CHAR1"] = "   ";
                    dwData["CHAR2"] = "   ";
                    dwData["CHAR3"] = "   ";
                    dwData["CHAR4"] = "   ";
                    dwData["CHAR5"] = "   ";
                }
                dwData["VKPAUFKRS"] = pm["KRS"].ToString();
                dwData["VKPAUFLFD"] = pm["LFD"].ToString();
                dwData["VKPAUFPOS"] = pm["POS"].ToString();
                dwData["VKPNEUDAT"] = date;
                dwData["ITEM"] = " ";
                dwData["VKPART"] = "PleaseEnter";
                dwData["CUSTOMER"] = (string)pm["CUSTOMER"];
                dwData["QUANTITY"] = 1;
                Helpers.Helper.GetSTime(ref date, ref time, ref stime);
                //MessageBox.Show(time);
                dwData["VKPNEUTIME"] = date+stime;
                dwData["VKPNUEBEN"] = (string)pm["CUSER"];
                dwData["ROWNID"] = dwData["VKPAUFKRS"].ToString() + "." + dwData["VKPAUFLFD"].ToString() + "." + Helpers.Helper.Right("000" + dwData["VKPAUFPOS"].ToString(), 3);
                dwData["VKPARTDES"] = "0";
                dwData["VKPFARBE"] = "0";
                dwData["VKPCDES"] = "0";
                dwData["TWIDTH"] = "0";
                dwData["VWIDTH"] = "0";
                dwData["GSQM"] = "0";
                dwData["GM"] = "0";
                dwData["YARN"] = "0";
                dwData["PRICE"] = "0";
                dwData["SPRICE"] = "0";
                dwData["COMPOSITION"] = "0";
                dwData["ORDTYPE"] = "0";
                dwData["QUANTITY"] = "0";
                dwData["VKPNUEBEN"] = "0";
                dwData["REMARK"] = " ";
                TBMain.Rows.InsertAt(dwData, 0);
                InsertICC_EXORD(dwData);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace, ex.Message);
            }
            return dwData;
        }
        public void SAVEVKP(Dictionary<string, object> pm)
        {
            if (TBMain.Rows.Count == 0)
                return;
            string _LFD = pm["LFD"].ToString();

            SQLiteDatabase.ExecuteNonQuery("DELETE FROM ICC_EXORD WHERE CAST(RTRIM(VKPAUFLFD) AS INT)='" + _LFD + "' ");
            filter = "VKPART like 'PleaseEnter%' OR ITEM like '0%' ";
            rows = TBMain.Select(filter);
            foreach (DataRow row in rows)
            {
                row.Delete();
            }
            TBMain.AcceptChanges();
            dwData = TBMain.NewRow();
            foreach (DataRow row in TBMain.Rows)
            {
                string VKPART = row["VKPART"].ToString();
                if (VKPART.StartsWith("PleaseEnter"))
                    continue;
                InsertICC_EXORD(row);
            }
            sql = "DELETE FROM ICC_EXORD WHERE VKPART like 'PleaseEnter%' ";
            SQLiteDatabase.ExecuteNonQuery(sql);
        }
        public void SETVKPBYART(Dictionary<string, object> pm)
        {
            double USD2RMB = (double)pm["USD2RMB"];
            DataRow dwVKP = (DataRow)pm["dwVKP"];
            double RATE1 = (double)pm["RATE1"];
            dwVKP["ITEM"] = pm["VFART"].ToString().Trim().ToUpper() + "-" + pm["VCOLOR"].ToString().Trim().ToUpper();
            dwVKP["VKPART"] = Helpers.Helper.Left(pm["VFART"].ToString().Trim().PadRight(20), 20);
            //dwVKP["VKPARTDES"] = pm["VKPARTDES"].ToString().Trim();
            if (pm["VKPARTDES"].ToString().Trim().Length>0)
            {
                dwVKP["ITEM"] = pm["VFART"].ToString().Trim().ToUpper() + pm["VKPARTDES"].ToString().TrimEnd().ToUpper() + "-" + pm["VCOLOR"].ToString().Trim().ToUpper();
                dwVKP["VKPART"] = Helpers.Helper.Left(pm["VFART"].ToString().Trim().PadRight(20), 20);
                sql = "SELECT * FROM ICC_EXART WHERE RTRIM(ARTICLE) = '" + pm["VFART"].ToString().ToUpper() + pm["VKPARTDES"].ToString().ToUpper().TrimEnd() + "'";
            }
            else
            {
                dwVKP["ITEM"] = pm["VFART"].ToString().Trim().ToUpper() + "-" + pm["VCOLOR"].ToString().Trim().ToUpper();
                dwVKP["VKPART"] = Helpers.Helper.Left(pm["VFART"].ToString().Trim().PadRight(20), 20);
                sql = "SELECT * FROM ICC_EXART WHERE RTRIM(ARTICLE) = '" + pm["VFART"].ToString().ToUpper().Trim() + "'";
            }          
            DataTable TBResult = SQLiteDatabase.GetDataTable(sql);
            DataRow vart = null;
            foreach (DataRow art in TBResult.Rows)
            {
                vart = art;
            }
            if (vart != null)
            {
                //dwVKP["VKPARTDES"] = vart["DESCRIPTION"];
                if (pm["VKPARTDES"].ToString().Trim().Length>0)
                {
                    dwVKP["VKPARTDES"] = pm["VKPARTDES"];
                }
                else
                {
                    dwVKP["VKPARTDES"] = vart["DESCRIPTION"];
                }

                dwVKP["TWIDTH"] = vart["WIDTH"];
                dwVKP["VWIDTH"] = vart["WIDTH"];
                dwVKP["GSQM"] = vart["WEIGHT"];
                dwVKP["GM"] = vart["WEIGHT"];
                dwVKP["YARN"] = vart["YARN"];
                //double price = Convert.ToDouble(vart["PRICE"].ToString());

                dwVKP["PRICE"] = vart["PRICE"];
              
                dwVKP["CHAR1"] = vart["CODE"].ToString().Substring(0,20);
                dwVKP["CHAR2"] = vart["BOOK"].ToString().Substring(0, 20);
                dwVKP["CHAR3"] = vart["LOCATION"].ToString().Substring(0, 20);
                dwVKP["CHAR4"] = vart["SUPPLIER"];
                dwVKP["COMPOSITION"] = vart["COMPOSITION"];
                dwVKP["VKPCDES"] = vart["REMARK"].ToString().PadRight(20).Substring(0,20);
            } 
            dwVKP["REMARK"] = pm["REMARK"].ToString(); 
            UEXORD1(dwVKP);
        }
        Thread pr;
        public void PRINT(Dictionary<string, object> pm)
        {
            PM = pm;

            InitializeWorkbook(@"C:\IntexReport\Report\TST\TSTEXVK.xls");
            string _LFD = PM["LFD"].ToString();
            sql = "SELECT * FROM ICC_EXORD  WHERE  VKPART NOT like 'PleaseEnter%' AND RTRIM(VKPAUFLFD)='" + _LFD + "' ";
            DataTable TBResult = SQLiteDatabase.GetDataTable(sql);
            TBResult.TableName = "ICC_EXORD";
            DataSet Set = new DataSet();
            Set.Merge(TBResult);
            TSTEXAPP(Set);
            return;
            pr = new Thread(StartPrint);
            pr.IsBackground = true;
            pr.ApartmentState = ApartmentState.STA;
            pr.Start();

        }
        private void TSTEXAPP(DataSet db)
        {
            try
            {
                ISheet sheet1 = hssfworkbook.GetSheet("TSTEXORD");

                //sheet1.CreateRow(0).CreateCell(0).SetCellValue("");

                string value = "";
                int x = 1, y = 1;
                int rown = 2;
                string processed = "";
                int idx = 0;
                int rowstart = 9;
                DataRow dwVKP;
                dwVKP = db.Tables["ICC_EXORD"].Rows[0];
                #region===Header============
                sheet1.CreateRow(5 - 1).CreateCell(4 - 1).SetCellValue(dwVKP["CUSTOMER"].ToString().Trim());
                sheet1.CreateRow(5 - 1).CreateCell(30 - 1).SetCellValue(dwVKP["VKPAUFKRS"].ToString() + dwVKP["VKPAUFLFD"].ToString());
                sheet1.CreateRow(6 - 1).CreateCell(4 - 1).SetCellValue(dwVKP["CHAR1"].ToString().Trim());
                sheet1.CreateRow(6 - 1).CreateCell(14 - 1).SetCellValue(dwVKP["CHAR2"].ToString().Trim());
                sheet1.CreateRow(6 - 1).CreateCell(20 - 1).SetCellValue(dwVKP["CHAR3"].ToString().Trim());
                sheet1.CreateRow(6 - 1).CreateCell(30 - 1).SetCellValue(year + "/" + month + "/" + day);
                #endregion
                int count = db.Tables["ICC_EXORD"].Rows.Count;
                foreach (DataRow row in db.Tables["ICC_EXORD"].Rows)
                {
                    if (row["VKPART"].ToString().Trim() == "") continue;

                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(1 - 1).SetCellValue(row["VKPAUFPOS"].ToString().Trim());
                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(3 - 1).SetCellValue(row["VKPART"].ToString().Trim());
                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(7 - 1).SetCellValue(row["VKPFARBE"].ToString().Trim());
                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(11 - 1).SetCellValue(row["QUANTITY"].ToString().Trim());
                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(22 - 1).SetCellValue(row["SPRICE"].ToString().Trim());// + row["CHAR1"].ToString().Trim());
                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(17 - 1).SetCellValue(row["VWIDTH"].ToString().Trim() + "-" + row["TWIDTH"].ToString().Trim() + "CM");
                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(13 - 1).SetCellValue(row["GSQM"].ToString().Trim() + "g/sqm");
                    sheet1.CreateRow(rowstart + rown * idx - 1).CreateCell(25 - 1).SetCellValue(row["COMPOSITION"].ToString());
                    //exAgent.SetCellValue(rowstart+  rown * idx, 25, row["GM"].ToString().Trim() + "g/m");
                    //exAgent.SetCellValue(rowstart+  rown * idx, 28, row["YARN"].ToString().Trim() );
                    sheet1.CreateRow(rowstart + rown * idx + 1 - 1).CreateCell(1 - 1).SetCellValue("Remark:");
                    sheet1.CreateRow(rowstart + rown * idx + 1 - 1).CreateCell(3 - 1).SetCellValue(row["REMARK"].ToString());
                    //exAgent.SetCellValue(rowstart+ rown * idx+3, 3, "note:"+row["REMARK"].ToString());
                    idx++;
                }
                sheet1.CreateRow(rowstart + rown * idx + 1 - 1).CreateCell(23 - 1).SetCellValue("Total:" + idx + " items");
                WriteToFile(dwVKP["VKPAUFKRS"].ToString() + dwVKP["VKPAUFLFD"].ToString());
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        void WriteToFile(string filename)
        {
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"C:\IntexReport\" + filename + ".xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
            System.Diagnostics.Process.Start("EXCEL.EXE", @"C:\IntexReport\" + filename + ".xls");
        }
        HSSFWorkbook hssfworkbook;
        void InitializeWorkbook(string path)
        {
            FileStream file = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);

            hssfworkbook = new HSSFWorkbook(file);

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }
        public void StartPrint()
        {
            string _LFD = PM["LFD"].ToString();
            sql = "SELECT * FROM ICC_EXORD  WHERE  VKPART NOT like 'PleaseEnter%' AND RTRIM(VKPAUFLFD)='" + _LFD + "' ";
            DataTable TBResult = SQLiteDatabase.GetDataTable(sql);
            TBResult.TableName = "ICC_EXORD";
            DataSet Set = new DataSet();
            Set.Merge(TBResult);
            VDRAUFTR cDR = new VDRAUFTR();
            cDR.DocType = "AFSEXAPP";
            cDR.PRINT(Set);
        }
    }

    public partial class VICC_EXORD
    {

        public string ROWNID { get { if (dwMaster == null) return ""; return dwMaster["ROWNID"].ToString(); } set { if (dwMaster == null) return; dwMaster["ROWNID"] = value; OnPropertyChanged("ROWNID", value); } } //lENGth 20
        public string VKPAUFKRS { get { if (dwMaster == null) return ""; return dwMaster["VKPAUFKRS"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPAUFKRS"] = value; OnPropertyChanged("VKPAUFKRS", value); } } //lENGth 2
        public string VKPAUFLFD { get { if (dwMaster == null) return ""; return dwMaster["VKPAUFLFD"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPAUFLFD"] = value; OnPropertyChanged("VKPAUFLFD", value); } } //lENGth 6
        public string VKPAUFPOS { get { if (dwMaster == null) return ""; return dwMaster["VKPAUFPOS"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPAUFPOS"] = value; OnPropertyChanged("VKPAUFPOS", value); } } //lENGth 3
        public string ITEM { get { if (dwMaster == null) return ""; return dwMaster["ITEM"].ToString(); } set { if (dwMaster == null) return; dwMaster["ITEM"] = value; OnPropertyChanged("ITEM", value); } } //lENGth 40
        public string VKPART { get { if (dwMaster == null) return ""; return dwMaster["VKPART"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPART"] = value; OnPropertyChanged("VKPART", value); } } //lENGth 20
        public string VKPARTDES { get { if (dwMaster == null) return ""; return dwMaster["VKPARTDES"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPARTDES"] = value; OnPropertyChanged("VKPARTDES", value); } } //lENGth 40
        public string VKPFARBE { get { if (dwMaster == null) return ""; return dwMaster["VKPFARBE"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPFARBE"] = value; OnPropertyChanged("VKPFARBE", value); } } //lENGth 10
        public string VKPCDES { get { if (dwMaster == null) return ""; return dwMaster["VKPCDES"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPCDES"] = value; OnPropertyChanged("VKPCDES", value); } } //lENGth 40
        public string TWIDTH { get { if (dwMaster == null) return ""; return dwMaster["TWIDTH"].ToString(); } set { if (dwMaster == null) return; dwMaster["TWIDTH"] = value; OnPropertyChanged("TWIDTH", value); } } //lENGth 10
        public string VWIDTH { get { if (dwMaster == null) return ""; return dwMaster["VWIDTH"].ToString(); } set { if (dwMaster == null) return; dwMaster["VWIDTH"] = value; OnPropertyChanged("VWIDTH", value); } } //lENGth 10
        public string GSQM { get { if (dwMaster == null) return ""; return dwMaster["GSQM"].ToString(); } set { if (dwMaster == null) return; dwMaster["GSQM"] = value; OnPropertyChanged("GSQM", value); } } //lENGth 10
        public string GM { get { if (dwMaster == null) return ""; return dwMaster["GM"].ToString(); } set { if (dwMaster == null) return; dwMaster["GM"] = value; OnPropertyChanged("GM", value); } } //lENGth 10
        public string YARN { get { if (dwMaster == null) return ""; return dwMaster["YARN"].ToString(); } set { if (dwMaster == null) return; dwMaster["YARN"] = value; OnPropertyChanged("YARN", value); } } //lENGth 40
        public string PRICE { get { if (dwMaster == null) return ""; return dwMaster["PRICE"].ToString(); } set { if (dwMaster == null) return; dwMaster["PRICE"] = value; OnPropertyChanged("PRICE", value); } } //lENGth 10
        public string SPRICE { get { if (dwMaster == null) return ""; return dwMaster["SPRICE"].ToString(); } set { if (dwMaster == null) return; dwMaster["SPRICE"] = value; OnPropertyChanged("SPRICE", value); } } //lENGth 10
        public string COMPOSITION { get { if (dwMaster == null) return ""; return dwMaster["COMPOSITION"].ToString(); } set { if (dwMaster == null) return; dwMaster["COMPOSITION"] = value; OnPropertyChanged("COMPOSITION", value); } } //lENGth 200
        public string ORDTYPE { get { if (dwMaster == null) return ""; return dwMaster["ORDTYPE"].ToString(); } set { if (dwMaster == null) return; dwMaster["ORDTYPE"] = value; OnPropertyChanged("ORDTYPE", value); } } //lENGth 10
        public string QUANTITY { get { if (dwMaster == null) return ""; return dwMaster["QUANTITY"].ToString(); } set { if (dwMaster == null) return; dwMaster["QUANTITY"] = value; OnPropertyChanged("QUANTITY", value); } } //lENGth 10
        public string CUSTOMER { get { if (dwMaster == null) return ""; return dwMaster["CUSTOMER"].ToString(); } set { if (dwMaster == null) return; dwMaster["CUSTOMER"] = value; OnPropertyChanged("CUSTOMER", value); } } //lENGth 8
        public string VKPNEUDAT { get { if (dwMaster == null) return ""; return dwMaster["VKPNEUDAT"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPNEUDAT"] = value; OnPropertyChanged("VKPNEUDAT", value); } } //lENGth 8
        public string VKPNEUTIME { get { if (dwMaster == null) return ""; return dwMaster["VKPNEUTIME"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPNEUTIME"] = value; OnPropertyChanged("VKPNEUTIME", value); } } //lENGth 20
        public string VKPNUEBEN { get { if (dwMaster == null) return ""; return dwMaster["VKPNUEBEN"].ToString(); } set { if (dwMaster == null) return; dwMaster["VKPNUEBEN"] = value; OnPropertyChanged("VKPNUEBEN", value); } } //lENGth 8
        public string REMARK { get { if (dwMaster == null) return ""; return dwMaster["REMARK"].ToString(); } set { if (dwMaster == null) return; dwMaster["REMARK"] = value; OnPropertyChanged("REMARK", value); } } //lENGth 80
        public string NUMBER1 { get { if (dwMaster == null) return ""; return dwMaster["NUMBER1"].ToString(); } set { if (dwMaster == null) return; dwMaster["NUMBER1"] = value; OnPropertyChanged("NUMBER1", value); } } //lENGth 10
        public string NUMBER2 { get { if (dwMaster == null) return ""; return dwMaster["NUMBER2"].ToString(); } set { if (dwMaster == null) return; dwMaster["NUMBER2"] = value; OnPropertyChanged("NUMBER2", value); } } //lENGth 10
        public string NUMBER3 { get { if (dwMaster == null) return ""; return dwMaster["NUMBER3"].ToString(); } set { if (dwMaster == null) return; dwMaster["NUMBER3"] = value; OnPropertyChanged("NUMBER3", value); } } //lENGth 10
        public string NUMBER4 { get { if (dwMaster == null) return ""; return dwMaster["NUMBER4"].ToString(); } set { if (dwMaster == null) return; dwMaster["NUMBER4"] = value; OnPropertyChanged("NUMBER4", value); } } //lENGth 10
        public string NUMBER5 { get { if (dwMaster == null) return ""; return dwMaster["NUMBER5"].ToString(); } set { if (dwMaster == null) return; dwMaster["NUMBER5"] = value; OnPropertyChanged("NUMBER5", value); } } //lENGth 10
        public string CHAR1 { get { if (dwMaster == null) return ""; return dwMaster["CHAR1"].ToString(); } set { if (dwMaster == null) return; dwMaster["CHAR1"] = value; OnPropertyChanged("CHAR1", value); } } //lENGth 20
        public string CHAR2 { get { if (dwMaster == null) return ""; return dwMaster["CHAR2"].ToString(); } set { if (dwMaster == null) return; dwMaster["CHAR2"] = value; OnPropertyChanged("CHAR2", value); } } //lENGth 20
        public string CHAR3 { get { if (dwMaster == null) return ""; return dwMaster["CHAR3"].ToString(); } set { if (dwMaster == null) return; dwMaster["CHAR3"] = value; OnPropertyChanged("CHAR3", value); } } //lENGth 20
        public string CHAR4 { get { if (dwMaster == null) return ""; return dwMaster["CHAR4"].ToString(); } set { if (dwMaster == null) return; dwMaster["CHAR4"] = value; OnPropertyChanged("CHAR4", value); } } //lENGth 20
        public string CHAR5 { get { if (dwMaster == null) return ""; return dwMaster["CHAR5"].ToString(); } set { if (dwMaster == null) return; dwMaster["CHAR5"] = value; OnPropertyChanged("CHAR5", value); } } //lENGth 20


    }


}
